﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Text;

namespace PPIMobileInterface
{
    /// <summary>
    /// A structure representing a pole Id or Index
    /// </summary>
    public struct PoleId
    {
        public Int32 _Num;
        public bool _NumIsId;
    }

    /// <summary>
    /// Enumerates the setting types
    /// </summary>
    public enum Settings
    {
        Timezone = 1,
        Utility = 2,
        Project = 3,
        Vendor = 4,
        User = 5,
        Handheld = 6,
        MobileVersion = 7
    }

    /// <summary>
    /// Various utilities.
    /// </summary>
    public static class Utils
    {
        #region DB
        /// <summary>
        /// Opens and returns a sql connection
        /// </summary>
        public static SqlCeConnection GetConnection()
        {
            return GetConnection(PPIConstants.K_ConnectionString);
        }
        public static SqlCeConnection GetConnection(string connString)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.21
            //Last updated by: VMCoder
            //Last update: 2008.04.21

            try
            {
                if (connString.IndexOf("Data Source") < 0)
                    connString = "Data Source = " + connString + ";";
                SqlCeConnection _Con = new SqlCeConnection(connString);
                if (_Con.State != ConnectionState.Open)
                    _Con.Open();

                return _Con;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// Tests database against Pole Position Mobile Standards
        /// </summary>
        public static bool TestDatabase(string _ConnString)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.21
            //Last updated by: VMCoder
            //Last update: 2008.04.21

            string _Sql;
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                _Con = GetConnection(_ConnString);

                #region tb_Vendor_Visits
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    VisitId,";
                _Sql += "\n    AssetFk,";
                _Sql += "\n    VisitTypeFk,";
                _Sql += "\n    Latitude,";
                _Sql += "\n    Longitude,";
                _Sql += "\n    Altitude,";
                _Sql += "\n    SatelliteCount,";
                _Sql += "\n    SatelliteTime,";
                _Sql += "\n    SolutionAvailable,";
                _Sql += "\n    UtilityFk,";
                _Sql += "\n    ProjectFk,";
                _Sql += "\n    VendorFk,";
                _Sql += "\n    UserFk,";
                _Sql += "\n    Handheld,";
                _Sql += "\n    TopTypeFk";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visits";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visits

                #region tb_Vendor_Visit_XStructureMappingData
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    VisitFk,";
                _Sql += "\n    DataId,";
                _Sql += "\n    AID";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visit_XStructureMappingData";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visit_XStructureMappingData

                #region tb_Vendor_Visit_XStructureMappingPoles
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    LVisitor,";
                _Sql += "\n    PoleId,";
                _Sql += "\n    DataFk,";
                _Sql += "\n    TopTypeFk,";
                _Sql += "\n    SpeciesFk,";
                _Sql += "\n    TreatmentFk,";
                _Sql += "\n    Year,";
                _Sql += "\n    ClassFk,";
                _Sql += "\n    Latitude,";
                _Sql += "\n    Longitude,";
                _Sql += "\n    Altitude,";
                _Sql += "\n    SatelliteCount,";
                _Sql += "\n    SatelliteTime,";
                _Sql += "\n    SolutionAvailable,";
                _Sql += "\n    AID,";
                _Sql += "\n    LVisit,";
                _Sql += "\n    Number,";
                _Sql += "\n    Length";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visit_XStructureMappingPoles";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visit_XStructureMappingPoles

                #region tb_Vendor_Visit_Images
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    PoleFk,";
                _Sql += "\n    Filename,";
                _Sql += "\n    SolutionAvailable,";
                _Sql += "\n    SatelliteTime,";
                _Sql += "\n    SatelliteCount,";
                _Sql += "\n    Altitude,";
                _Sql += "\n    Longitude,";
                _Sql += "\n    Latitude,";
                _Sql += "\n    Direction,";
                _Sql += "\n    VisitFk,";
                _Sql += "\n    ImageId";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visit_Images";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visit_Images

                #region tb_Tops
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    TopId,";
                _Sql += "\n    Name,";
                _Sql += "\n    VisitTypeFk";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Tops";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Tops

                #region tb_Lookups
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    LookupId,";
                _Sql += "\n    LookupTypeFk,";
                _Sql += "\n    LookupText,";
                _Sql += "\n    LookupValue";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Lookups";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Lookups

                #region tb_Settings
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    SettingId,";
                _Sql += "\n    SettingKey,";
                _Sql += "\n    SettingName,";
                _Sql += "\n    SettingValue";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Settings";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Settings

                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (_Con != null)
                    _Con.Close();
            }
        }
        /// <summary>
        /// Creates and returns a sql parameter
        /// </summary>
        public static SqlCeParameter CreateParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, bool isNullable, byte precision, byte scale, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return new SqlCeParameter(parameterName, dbType, size, direction, isNullable, precision, scale, String.Empty, DataRowVersion.Default, value);
        }
        /// <summary>
        /// Creates and returns a sql Int parameter
        /// </summary>
        public static SqlCeParameter CreateIntParameter(string parameterName, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.Int, 0, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql Bit parameter
        /// </summary>
        public static SqlCeParameter CreateBitParameter(string parameterName, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.Bit, 0, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql DateTime parameter
        /// </summary>
        public static SqlCeParameter CreateDateTimeParameter(string parameterName, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.DateTime, 0, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql NVarChar parameter
        /// </summary>
        public static SqlCeParameter CreateNVarCharParameter(string parameterName, int size, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.NVarChar, size, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql Decimal parameter
        /// </summary>
        public static SqlCeParameter CreateDecimalParameter(string parameterName, ParameterDirection direction, bool isNullable, byte precision, byte scale, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.Decimal, 0, direction, isNullable, precision, scale, value);
        }

        public static bool AddAuditableColumns(string _ConnString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "ALTER TABLE tb_Vendor_Visit_Images ADD [ImageServerID] int,[PoleServerID] int,[StructureServerID] int,[UploadedDate] datetime";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "ALTER TABLE tb_Vendor_Visit_XStructureMappingPoles ADD [PoleServerID] int, [StructureServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "ALTER TABLE tb_Vendor_Visits ADD [StructureServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool AddProjectColumn(string _ConnString)
        {
            SqlCeConnection _MobileConnection = null;
            StringBuilder  _Query = new StringBuilder();
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query.Append("ALTER TABLE tb_Vendor_Visits ADD [ProjectFK] int");
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query.ToString(), _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertProject(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Projects";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();
                        
                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertTopType(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Tops";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertVisitType(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='VisitTypes'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }
        public static bool InsertClasses(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='Classes'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertSpecies(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='Species'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertTreatments(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='Treatments'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertContacts(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Users";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();

                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertUtilities(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Utility";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();

                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertVendors(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Vendor";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();

                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        /// <summary>
        /// Verifies if PROJECTS table was created. If not it will create it.
        /// </summary>
        /// <returns></returns>
        public static bool IsProjectTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Projects'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Projects");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   ProjectId INT PRIMARY KEY,");
                    _Sql.AppendLine("   VendorFk INT ,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Projects");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Verifies if UTILITIES table was created. If not it will create it
        /// </summary>
        /// <returns></returns>
        public static bool IsUtilityTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Utility'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Utility");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   UtilityID INT PRIMARY KEY,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(),_Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Utility");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Verifies if USERS table was created. If not it will create it
        /// </summary>
        /// <returns></returns>
        public static bool IsUsersTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Users'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Users");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   UserId INT PRIMARY KEY,");
                    _Sql.AppendLine("   VendorFk INT ,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(),_Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Users");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Verifies if Vendor table was created. If not it will create it
        /// </summary>
        /// <returns></returns>
        public static bool IsVendorTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Vendor'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Vendor");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   VendorId INT PRIMARY KEY,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Vendor");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        #endregion DB

        #region Conversion
        /// <summary>
        /// Converts an object to Int32 or returns Int32.MinValue if it is null or DBNull
        /// </summary>
        public static Int32 ToInt32(Object obj)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            try
            {
                return obj != null && obj != DBNull.Value ? Int32.Parse(obj.ToString()) : Int32.MinValue;
            }
            catch
            {
                return Int32.MinValue;
            }
        }
        /// <summary>
        /// Converts an object to double or returns double.MinValue if it is null or DBNull
        /// </summary>
        public static double Todouble(Object obj)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            try
            {
                return obj != null && obj != DBNull.Value ? double.Parse(obj.ToString()) : double.MinValue;
            }
            catch
            {
                return double.MinValue;
            }
        }
        /// <summary>
        /// Converts an object to DateTime or returns DateTime.MinValue if it is null or DBNull
        /// </summary>
        public static DateTime ToDateTime(Object obj)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            try
            {
                return obj != null && obj != DBNull.Value ? DateTime.Parse(obj.ToString()) : DateTime.MinValue;
            }
            catch
            {
                return DateTime.MinValue;
            }
        }
        /// <summary>
        /// Transforms longitude from GPS format to a decimal number.
        /// </summary>
        public static double ConvertLongitudeToDecimal(string Position, string CoordinatedLetter, ref string FormatedView)
        {
            //Created by: GBarzola
            //Creation date: -
            //Last updated by: GBarzola
            //Last update: -

            try
            {
                double SingleValue = 1;
                switch (CoordinatedLetter)
                {
                    case "E":
                        SingleValue = 1;
                        break;
                    case "W":
                        SingleValue = -1;
                        break;
                }
                double NewPosition = 0;
                FormatedView = "0";
                if (Position != "0")
                {
                    string[] arrPosition = Position.Split('.');
                    double previewMinutes = double.Parse(arrPosition[0].Substring(3, 2));
                    double previewDegree = double.Parse(arrPosition[0].Substring(0, 3));

                    double previewSeconds = (double.Parse(arrPosition[1]) / 10000000) * 60;
                    FormatedView = previewDegree.ToString() + " ° " + previewMinutes.ToString() + " ' " + previewSeconds.ToString() + " \" ";
                    // 34º 36' 34.79'' = 34 + ( 36 / 60 ) + ( 34,79 / 3600 ) =  (expresado en decimal)
                    NewPosition = (previewDegree + (previewMinutes / 60) + (previewSeconds / 3600)) * SingleValue;
                }
                return NewPosition;
            }
            catch (Exception ex)
            {
                return 0;
            }


        }
        /// <summary>
        /// Transforms latitude from GPS format to a decimal number.
        /// </summary>
        public static double ConvertLatitudeToDecimal(string Position, string CoordinatedLetter, ref string DegreeFormated)
        {
            //Created by: GBarzola
            //Creation date: -
            //Last updated by: GBarzola
            //Last update: -

            try
            {
                double SingleValue = 1;
                switch (CoordinatedLetter)
                {
                    case "N":
                        SingleValue = 1;
                        break;
                    case "S":
                        SingleValue = -1;
                        break;
                }
                double NewPosition = 0;
                DegreeFormated = "0";
                if (Position != "0")
                {
                    string[] arrPosition = Position.Split('.');
                    double previewMinutes = double.Parse(arrPosition[0].Substring(2, 2));
                    double previewDegree = double.Parse(arrPosition[0].Substring(0, 2));
                    double previewSeconds = (double.Parse(arrPosition[1]) / 10000000) * 60;
                    DegreeFormated = previewDegree.ToString() + " ° " + previewMinutes.ToString() + " ' " + previewSeconds.ToString() + " \" ";
                    // 34º 36' 34.79'' = 34 + ( 36 / 60 ) + ( 34,79 / 3600 ) =  (expresado en decimal)
                    NewPosition = (previewDegree + (previewMinutes / 60) + (previewSeconds / 3600)) * SingleValue;
                }
                return NewPosition;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        #endregion Conversion
    
        #region UI
        //public static List<Int32> FillLookupCombo(ComboBox combo, string lookupType)
        //{
        //    return FillLookupCombo(combo, lookupType, false, "");
        //}
        //public static List<Int32> FillLookupCombo(ComboBox combo, string lookupType, bool addNoneOption)
        //{
        //    return FillLookupCombo(combo, lookupType, addNoneOption, "[None]");
        //}
        ///// <summary>
        ///// Fills a combo with data from the Lookups table, with or without starting "[None]" option
        ///// </summary>
        //public static List<Int32> FillLookupCombo(ComboBox combo, string lookupType, bool addNoneOption, string noneOption)
        //{
        //    //Created by: VMCoder
        //    //Creation date: -
        //    //Last updated by: VMCoder
        //    //Last update: 2008.04.10

        //    List<Int32> _LookupValues = new List<Int32>();
        //    DataTable _Dt = new DataTable();
        //    combo.BeginUpdate();
        //    combo.Items.Clear();
        //    if (addNoneOption)
        //    {
        //        _LookupValues.Add(0);
        //        combo.Items.Add(noneOption);
        //    }
        //    _Dt = LogicLayer.SelectLookupsXType(lookupType);
        //    foreach (DataRow _Dr in _Dt.Rows)
        //    {
        //        _LookupValues.Add(Utils.ToInt32(_Dr["LookupValue"]));
        //        combo.Items.Add(_Dr["LookupText"].ToString());
        //    }
        //    if (combo.Items.Count > 0)
        //        combo.SelectedIndex = 0;
        //    combo.EndUpdate();
        //    return _LookupValues;
        //}
        //public static List<Int32> FillTopsCombo(ComboBox combo, Int32 visitTypeFk)
        //{
        //    return FillTopsCombo(combo, visitTypeFk, false, "");
        //}
        //public static List<Int32> FillTopsCombo(ComboBox combo, Int32 visitTypeFk, bool addNoneOption)
        //{
        //    return FillTopsCombo(combo, visitTypeFk, addNoneOption, "[None]");
        //}
        ///// <summary>
        ///// Fills a combo with data from the Tops table, with or without starting "[None]" option
        ///// </summary>
        //public static List<Int32> FillTopsCombo(ComboBox combo, Int32 visitTypeFk, bool addNoneOption, string noneOption)
        //{
        //    //Created by: VMCoder
        //    //Creation date: -
        //    //Last updated by: VMCoder
        //    //Last update: 2008.04.10

        //    List<Int32> _LookupValues = new List<Int32>();
        //    DataTable _Dt = new DataTable();
        //    combo.BeginUpdate();
        //    combo.Items.Clear();
        //    if (addNoneOption)
        //    {
        //        _LookupValues.Add(0);
        //        combo.Items.Add(noneOption);
        //    }
        //    if (visitTypeFk > 0)
        //    {
        //        _Dt = LogicLayer.SelectTopsXVisitType(visitTypeFk);
        //        foreach (DataRow _Dr in _Dt.Rows)
        //        {
        //            _LookupValues.Add(Utils.ToInt32(_Dr["TopId"]));
        //            combo.Items.Add(_Dr["Name"].ToString());
        //        }
        //    }
        //    if (combo.Items.Count > 0)
        //        combo.SelectedIndex = 0;
        //    combo.EndUpdate();
        //    return _LookupValues;
        //}
        //public static void FillNumericCombo(ComboBox combo, Int32 startingValue, Int32 endingValue, Int32 step)
        //{
        //    FillNumericCombo(combo, startingValue, endingValue, step, false, "");
        //}
        //public static void FillNumericCombo(ComboBox combo, Int32 startingValue, Int32 endingValue, Int32 step, bool addNoneOption)
        //{
        //    FillNumericCombo(combo, startingValue, endingValue, step, addNoneOption, "[None]");
        //}
        ///// <summary>
        ///// Fills a combo with numeric values from a given range, with or without starting "[None]" option
        ///// </summary>
        //public static void FillNumericCombo(ComboBox combo, Int32 startingValue, Int32 endingValue, Int32 step, bool addNoneOption, string noneOption)
        //{
        //    //Created by: VMCoder
        //    //Creation date: -
        //    //Last updated by: VMCoder
        //    //Last update: 2008.04.10

        //    combo.BeginUpdate();
        //    combo.Items.Clear();
        //    if (addNoneOption)
        //        combo.Items.Add(noneOption);
        //    if (endingValue > startingValue)
        //        for (Int32 _I = startingValue; _I <= endingValue; _I += step)
        //            combo.Items.Add(_I);
        //    else
        //        for (Int32 _I = startingValue; _I >= endingValue; _I -= step)
        //            combo.Items.Add(_I);
        //    if (combo.Items.Count > 0)
        //        combo.SelectedIndex = 0;
        //    combo.EndUpdate();
        //}
        #endregion UI

        #region IO
        /// <summary>
        /// Copies a directory from one path to another with all subdirectories and files
        /// </summary>
        public static void CopyDirectory(string source, string destination)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            if (destination[destination.Length - 1] != Path.DirectorySeparatorChar) 
                destination += Path.DirectorySeparatorChar;
            if(!Directory.Exists(destination)) 
                Directory.CreateDirectory(destination);
            foreach(string _Element in Directory.GetFileSystemEntries(source))
            {
                if(Directory.Exists(_Element)) 
                    CopyDirectory(_Element, destination+Path.GetFileName(_Element));
                else 
                    File.Copy(_Element, destination+Path.GetFileName(_Element), true);
            }
        }
        #endregion IO
    }
}
